
import openpyxl
from openpyxl import Workbook 
import re
import os



data = [
    ['证券代码', '证券名称', '2013年', '2014年', '2015年', '2016年', '2017年', '2018年', '2019年', '2020年', '2021年', '2022年', '2023年', '2013年排名', '2014年排名', '2015年排名', '2016年排名', '2017年排名', '2018年排名', '2019年排名', '2020年排名', '2021年排名', '2022年排名', '2023年排名'],
['000568.SZ', '泸州老窖', 56.98, 47.61, 49.4, 62.43, 71.93, 77.53, 80.62, 83.05, 85.7, 86.59, 88.3, 13, 17, 17, 11, 8, 5, 3, 3, 2, 2, 2],
['000596.SZ', '古井贡酒', 69.78, 68.63, 71.27, 74.68, 76.43, 77.76, 76.71, 75.23, 75.1, 77.17, 79.07, 7, 4, 3, 4, 4, 4, 5, 6, 8, 6, 4],
['000799.SZ', '酒鬼酒', 71.11, 59.87, 70.47, 74.99, 77.94, 78.83, 77.75, 78.87, 79.97, 79.63, 78.35, 4, 11, 4, 3, 3, 3, 4, 4, 4, 4, 6],
['000858.SZ', '五 粮 液', 73.26, 72.53, 69.2, 70.2, 72.01, 73.8, 74.46, 74.16, 75.35, 75.42, 75.79, 3, 2, 7, 7, 7, 7, 9, 8, 6, 8, 7],
['000860.SZ', '顺鑫农业', 31.2, 33.67, 37.28, 34.54, 33.92, 39.96, 36.2, 28.36, 27.91, 31.73, 31.8, 18, 18, 18, 17, 18, 17, 18, 17, 18, 17, 18],
['000995.SZ', '皇台酒业', 64.03, 60.32, 58.92, 18.74, 36.07, 24.07, 75.0, 66.79, 59.04, 63.82, 61.18, 9, 10, 12, 18, 17, 18, 7, 13, 15, 14, 15],
['002304.SZ', '洋河股份', 60.42, 60.62, 61.91, 63.9, 66.46, 73.7, 71.35, 72.27, 75.32, 74.6, 75.25, 11, 9, 9, 10, 11, 8, 12, 9, 7, 10, 9],
['002304.SZ', '洋河股份', 60.42, 60.62, 61.91, 63.9, 66.46, 73.7, 71.35, 72.27, 75.32, 74.6, 75.25, 11, 9, 9, 10, 11, 8, 12, 9, 7, 10, 9],
['600197.SH', '伊力特', 48.12, 52.35, 51.41, 50.21, 47.73, 49.01, 51.45, 48.61, 51.45, 48.05, 48.23, 17, 15, 15, 16, 16, 16, 16, 16, 16, 16, 16],
['600199.SH', '金种子酒', 66.04, 62.35, 59.58, 55.26, 52.75, 51.25, 37.87, 27.72, 28.8, 26.45, 40.32, 8, 8, 11, 15, 15, 15, 17, 18, 17, 18, 17],
['600519.SH', '贵州茅台', 92.94, 92.74, 92.41, 91.51, 90.27, 91.55, 91.64, 91.68, 91.79, 92.09, 92.12, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1],
['600559.SH', '老白干酒', 50.72, 58.17, 56.94, 59.23, 62.42, 61.15, 61.47, 64.74, 67.32, 68.36, 67.15, 16, 12, 14, 14, 13, 13, 14, 14, 13, 12, 13],
['600702.SH', '舍得酒业', 60.41, 50.66, 50.44, 64.16, 74.62, 72.63, 76.2, 75.87, 77.81, 77.72, 74.5, 12, 16, 16, 9, 5, 10, 6, 5, 5, 5, 11],
['600779.SH', '水井坊', 69.93, 68.09, 75.3, 76.16, 79.06, 81.87, 82.87, 84.19, 84.51, 84.49, 83.16, 6, 5, 2, 2, 2, 2, 2, 2, 3, 3, 3],
['600809.SH', '山西汾酒', 75.07, 67.44, 67.37, 68.68, 69.84, 66.21, 71.92, 72.15, 74.91, 75.36, 75.31, 2, 7, 8, 8, 10, 11, 11, 10, 9, 9, 8],
['603198.SH', '迎驾贡酒', 53.58, 54.57, 58.33, 61.77, 60.64, 60.92, 64.36, 67.13, 67.54, 68.02, 71.37, 15, 14, 13, 12, 14, 14, 13, 12, 12, 13, 12],
['603369.SH', '今世缘', 70.58, 70.59, 70.0, 70.96, 71.76, 72.91, 72.81, 71.13, 74.62, 76.6, 78.35, 5, 3, 5, 6, 9, 9, 10, 11, 10, 7, 6],
['603589.SH', '口子窖', 62.93, 67.59, 69.83, 72.44, 72.9, 74.37, 74.97, 75.17, 73.9, 74.16, 75.19, 10, 6, 6, 5, 6, 6, 8, 7, 11, 11, 10],
['603919.SH', '金徽酒', 54.62, 57.15, 60.1, 61.33, 63.01, 62.3, 60.72, 62.51, 63.74, 62.78, 62.44, 14, 13, 10, 13, 12, 12, 15, 15, 14, 15, 14],
]

def get_data_after_final_rank(data):
    first_rank_index = 0
    for index, year in enumerate(data[0]):
        if "排名" in year:
            first_rank_index = index   # 获取第一个排名index
            break
    print(first_rank_index)
    # 添加综合排名的标题
    data[0].append('综合排名')

    # 计算综合排名
    for i in range(1, len(data)):
        # 获取该证券的排名数据
        rankings = data[i][first_rank_index:]  # 排名数据从第一个排名开始
        # print(rankings)
        # 计算平均排名，排除非数字值
        average_rank = sum(int(ranking) for ranking in rankings) / len(rankings)
        # 四舍五入保留两位小数
        average_rank = round(average_rank, 2)
        # 添加平均排名到数据行的末尾
        data[i].append(average_rank)

    # for row in data:
    #     print(row)

    # 按照综合排名排序
    sorted_data = sorted(data[1:], key=lambda x: float(x[-1]))

    # 将标题行添加回排序后的数据
    sorted_data.insert(0, data[0])

    # 打印排序后的结果
    for row in sorted_data:
        print(row)
